﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{

    public partial class C_OilCardInfoDAL : BaseDAL<C_OilCardInfo>
    {
        public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;
        #region
        //油卡基础信息列表查询
        public object GetCardList(int pageSize, int pageIndex, string isFanBan, string isActive, string cardCode, string driverWorkID, string driverName,
                                  string centerID,string stationID ,E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<C_OilCardInfoExt> list = new List<C_OilCardInfoExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=oci.ID,卡号=oci.CardCode,司机工号=oci.DriverWorkID,司机姓名=oci.DriverName,
                        备注=oci.Remark,卡内金额=cast(oci.CardAmount as decimal(18,2)),填写人=oci.FillPerson,填写时间=oci.FillTime,是否有效=oci.IsActive
                        ,关联卡号=oci.LinkCardCode,是否翻班=oci.IsFanBan,分中心=tc.名称,分站=ts.名称,分中心编码=oci.CenterID,分站编码=oci.StationID
                        into #tempa                   
                        from C_OilCardInfo oci ");
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on tp.工号=oci.DriverWorkID ");
            sb.Append(" left join  P_User  pu  on pu.WorkCode = oci.DriverWorkID ");
            sb.Append(" left join  P_Department  pd  on pd.ID = pu.DepID ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on oci.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on oci.CenterID=tc.编码 ");
            sb.Append(" where 1=1 ");
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("oci.IsActive", isActive, sb);
                    WhereClauseUtility.AddStringEqual("oci.IsFanBan", isFanBan, sb);
                    WhereClauseUtility.AddStringEqual("oci.CardCode", cardCode, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverWorkID", driverWorkID, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverName", driverName, sb);
                    WhereClauseUtility.AddStringEqual("oci.CenterID", centerID, sb);
                    WhereClauseUtility.AddStringEqual("oci.StationID", stationID, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("oci.IsFanBan", isFanBan, sb);
                    WhereClauseUtility.AddStringEqual("oci.IsActive", isActive, sb);
                    WhereClauseUtility.AddStringEqual("oci.CardCode", cardCode, sb);
                    WhereClauseUtility.AddStringEqual("pd.DispatchSubCenterID", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("oci.StationID", stationID, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverWorkID", driverWorkID, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverName", driverName, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {   
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("oci.IsFanBan", isFanBan, sb);
                    WhereClauseUtility.AddStringEqual("oci.IsActive", isActive, sb);
                    WhereClauseUtility.AddStringEqual("oci.CardCode", cardCode, sb);
                    WhereClauseUtility.AddStringEqual("pd.DispatchSationID", selfStationID, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverWorkID", driverWorkID, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverName", driverName, sb);
                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("oci.IsFanBan", isFanBan, sb);
                    WhereClauseUtility.AddStringEqual("oci.IsActive", isActive, sb);
                    WhereClauseUtility.AddStringEqual("oci.DriverWorkID", selfWorkCode, sb);
                }

                sb.Append(" order by 填写时间 desc ");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        C_OilCardInfoExt info = new C_OilCardInfoExt();
                        info.ID = Convert.ToInt32(dr["编码"]);
                        info.CardCode = dr["卡号"].ToString();
                        info.CardAmount = dr["卡内金额"].ToString();
                        info.DriverWorkID = dr["司机工号"].ToString();
                        info.DriverName = dr["司机姓名"].ToString();
                        info.IsActive = Convert.ToBoolean(dr["是否有效"]);
                        info.Remark = dr["备注"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.LinkCardCode = dr["关联卡号"].ToString();
                        info.IsFanBan = Convert.ToBoolean(dr["是否翻班"]);
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.CenterID = dr["分中心编码"].ToString();
                        info.StationID = dr["分站编码"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }
        #endregion

        //获取卡号(所有的)
        public object GetCardCodeList(E_StatisticsPermisson em, string selfCenterID, string selfStationID, string selfWorkCode)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct CardCode from C_OilCardInfo oci  where 1=1");
            if (em == E_StatisticsPermisson.ALL)
            { sb.Append("and 1=1"); }
            else if (em == E_StatisticsPermisson.CENTER)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.STATION)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.SELF)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            sb.Append(" order by CardCode ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["CardCode"].ToString();
                    info.Name = dr["CardCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取卡号(有效的)
        public object GetYXCardCodeList(E_StatisticsPermisson em, string selfCenterID, string selfStationID, string selfWorkCode)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select CardCode from C_OilCardInfo oci ");
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on tp.工号=oci.DriverWorkID ");
            //sb.Append(" left join  P_User  pu  on pu.WorkCode = oci.DriverWorkID ");
            //sb.Append(" left join  P_Department  pd  on pd.ID = pu.DepID ");
            sb.Append(" where oci.IsActive=1 ");           

            if (em == E_StatisticsPermisson.ALL)
            { sb.Append("and 1=1"); }
            else if (em == E_StatisticsPermisson.CENTER)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.STATION)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.SELF)
            { WhereClauseUtility.AddStringEqual("oci.CenterID", selfCenterID, sb); }
            sb.Append(" order by CardCode ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["CardCode"].ToString();
                    info.Name = dr["CardCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取卡号(所有的)判断卡号重复存在的
        public object GetYXCardCodes()
        {
            List<CardList> list = new List<CardList>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select CardCode from C_OilCardInfo  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CardList info = new CardList();                   
                    info.Name = dr["CardCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取绑定人姓名
        public object GetDriverNamesList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select Name from P_User  where IsActive=1  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取绑定人工号
        public object GetDriverWorkIDList(E_StatisticsPermisson em, string selfCenterID, string selfStationID, string selfWorkCode)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select WorkCode from P_User pu ");          
            sb.Append(" left join  P_Department  pd  on pd.ID = pu.DepID ");
            sb.Append(" where pu.IsActive=1 ");
            if(em==E_StatisticsPermisson.ALL)
            { sb.Append("and 1=1"); }
            else if(em==E_StatisticsPermisson.CENTER)
            { WhereClauseUtility.AddStringEqual("pd.DispatchSubCenterID", selfCenterID, sb); }
            else if (em == E_StatisticsPermisson.STATION)
            { WhereClauseUtility.AddStringEqual("pd.DispatchSationID", selfStationID, sb); }
            else if (em == E_StatisticsPermisson.SELF)
            { WhereClauseUtility.AddStringEqual("pu.WorkCode", selfWorkCode, sb); }

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["WorkCode"].ToString();
                    info.Name = dr["WorkCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取负责人工号
        public object GetWorkIDList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select WorkCode from P_User pu ");
            sb.Append(" where pu.IsActive=1 ");          

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["WorkCode"].ToString();
                    info.Name = dr["WorkCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //根据ID获取一条数据
        public C_OilCardInfo GetModelList(int ID)
        {
            List<C_OilCardInfo> list = new List<C_OilCardInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select  编码=ID,卡号=CardCode,司机工号=DriverWorkID,司机姓名=DriverName,卡内金额=CardAmount
                               ,是否有效=IsActive,备注=Remark,填写人=FillPerson,填写时间=FillTime,是否翻班=IsFanBan
                               ,关联卡号=LinkCardCode,分中心=CenterID,分站=StationID,卡内余额=bak1
                  from C_OilCardInfo  where ID='" + ID + "' ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardInfo info = new C_OilCardInfo();
                if (dr.Read())
                {
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.CardCode = dr["卡号"].ToString();
                    info.DriverWorkID = dr["司机工号"].ToString();
                    info.DriverName = dr["司机姓名"].ToString();
                    info.CardAmount = Convert.ToDecimal(dr["卡内金额"]);
                    info.IsActive = Convert.ToBoolean(dr["是否有效"]);
                    info.Remark = dr["备注"].ToString();
                    info.FillPerson = dr["填写人"].ToString();
                    info.FillTime = Convert.ToDateTime(dr["填写时间"]);
                    info.IsFanBan = Convert.ToBoolean(dr["是否翻班"]);
                    info.LinkCardCode = dr["关联卡号"].ToString();
                    info.CenterID = Convert.ToInt32(dr["分中心"]);
                    info.StationID = dr["分站"].ToString();
                    info.bak1 = dr["卡内余额"].ToString();
                    list.Add(info);
                }
                return info;
            }

        }
        //根据绑定工号获取绑定人姓名
        public C_OilCardLink GetOilCardLinkList(string workID)
        {
            List<C_OilCardLink> list = new List<C_OilCardLink>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 司机姓名=Name
                        from P_User  ");
            sb.Append(" where WorkCode='" + workID + "' ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardLink info = new C_OilCardLink();
                if (dr.Read())
                {
                    info.DriverName = dr["司机姓名"].ToString();
                    list.Add(info);
                }
                return info;
            }
        }
        //通过司机工号取姓名
        public object GetDriverNameList(string driverWorkID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select Name from P_User  where  WorkCode='"+driverWorkID+"' ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //通过姓名取工号
        public object GetWorkIDLists(string name)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select WorkCode from P_User  where  Name='" + name + "' ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["WorkCode"].ToString();
                    info.Name = dr["WorkCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取姓名
        public object GetNameList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select Name from P_User ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["Name"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取工号
        public object GetWorkID()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select WorkCode from P_User ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["WorkCode"].ToString();
                    info.Name = dr["WorkCode"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取和当前选中卡有关的卡的信息列表
        public object GetLink(int pageIndex, int pageSize, string linkCardCode)
        {
            List<C_OilCardInfoExt> list = new List<C_OilCardInfoExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=oci.ID,卡号=oci.CardCode,绑定工号=oci.DriverWorkID,绑定姓名=oci.DriverName,
                        备注=oci.Remark,卡内金额=cast(oci.CardAmount as decimal(18,2)),填写人=oci.FillPerson,填写时间=oci.FillTime,是否有效=oci.IsActive
                        ,关联卡号=oci.LinkCardCode,是否翻班=oci.IsFanBan,分中心=tc.名称,分站=ts.名称
                        into #tempa                   
                        from C_OilCardInfo oci ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on oci.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on oci.CenterID=tc.编码 ");
            sb.Append(" where oci.LinkCardCode='" + linkCardCode + "' ");
            sb.Append(" order by 填写时间 desc ");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + pageIndex + " A.*  from #temp A where 行号>" + (pageSize - 1) * pageIndex + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        C_OilCardInfoExt info = new C_OilCardInfoExt();
                        info.ID = Convert.ToInt32(dr["编码"]);
                        info.CardCode = dr["卡号"].ToString();
                        info.CardAmount = dr["卡内金额"].ToString();
                        info.DriverWorkID = dr["绑定工号"].ToString();
                        info.DriverName = dr["绑定姓名"].ToString();
                        info.IsActive = Convert.ToBoolean(dr["是否有效"]);
                        info.Remark = dr["备注"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.LinkCardCode = dr["关联卡号"].ToString();
                        info.IsFanBan = Convert.ToBoolean(dr["是否翻班"]);
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
       
    }

}

